﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using LibHuman;

namespace Human
{
    public partial class frmChamcong_thuongtruc : Form
    {
        string s_mmyy = "", s_ngaylv = "", s_bophan = "", m_user = "", user_mmyy = "", sql = "", m_hh = "", sql1 = "", user_dn = "";
        int p_thang = 0,p_nam=0,m_songay=0;
        AccessData mDB = new AccessData();
        DataSet ds_ngayle = new DataSet();
        DataSet ds_ngaybule = new DataSet();
        Language lan = new Language();
        DataSet ds = new DataSet();
        DataSet ds_tcden = new DataSet();
        bool m_admin = false,bcctc_taikhoa=false;
        public frmChamcong_thuongtruc(string m_mmyy,string m_ngay,string m_bphan,int m_month,int m_year,bool admin,string s_userdn)
        {
            InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            s_mmyy = m_mmyy;
            s_ngaylv = m_ngay;
            s_bophan = m_bphan;
            m_user = mDB.user;
            p_thang = m_month;
            p_nam = m_year;
            m_admin = admin;
            user_dn = s_userdn;
        }
        private void frmChamcong_Load(object sender, EventArgs e)
        {
            cmdChonkp.Enabled = m_admin;
            btn_xoa.Enabled = m_admin;
            cmdBoqua.Enabled = m_admin;
            DateTime d_ngay = mDB.StringToDateTime(s_ngaylv);
            m_hh = "n" + d_ngay.Day.ToString();
            m_songay = DateTime.DaysInMonth(p_nam, p_thang);
            txtngay.Text = s_ngaylv;
            bcctc_taikhoa = mDB.bcctc_taikhoa(s_mmyy);
            sql = "select * from " + m_user + ".bophan";
            cboKhoa.ValueMember = "madv";
            cboKhoa.DisplayMember = "tenbp";
            cboKhoa.DataSource = mDB.get_data(sql).Tables[0];
            cboKhoa.SelectedValue = s_bophan;
            ds_ngayle = mDB.get_ngaynghile(0, s_mmyy);
            ds_ngaybule = mDB.get_ngaynghile(1, s_mmyy);
            load_datagrid();
        }
        private void loadcalv()
        {
            string sql = "select * from nhansu.lichlv where ";
        }
        private DataSet get_nvtangcuong_den(string ngay_lv)
        {
            string sql2 = "";
            user_mmyy = m_user + s_mmyy;
            sql2 = " select  0 as stt,trim(l.ho)||' '||trim(l.ten) as hoten,l.phai,c.ten as chucdanh,cc.*";
            sql2 += " from " + m_user + ".llnv l inner join " + m_user + ".hopdong hd on l.manv=hd.manv ";
            sql2 += " inner join " + m_user + ".max_lan ml on hd.manv=ml.manv";
            sql2 += " inner join " + m_user + ".nv_tangcuong_" + s_mmyy + " ntc on l.manv=ntc.manv";
            sql2 += " inner join " + m_user + ".chamcong_thuongtruc_" + s_mmyy + " cc on l.manv=cc.manv";
            sql2 += " left join " + m_user + ".dmcd c on l.macd=c.ma";
            sql2 += " where hd.lan=ml.lan and hd.ngaynghi is null";
            sql2 += " and to_timestamp(to_char(ntc.tungay,'dd/mm/yyyy'),'dd/mm/yyyy') = ";
            sql2 += " (select max(to_timestamp(to_char(tc.tungay,'dd/mm/yyyy'),'dd/mm/yyyy')) from " + m_user + ".nv_tangcuong_" + s_mmyy + " tc where ntc.manv=tc.manv)";
            sql2 += " and (case ntc.denngay is not null when to_timestamp('" + ngay_lv + "','dd/mm/yyyy') between to_timestamp(to_char(ntc.tungay,'dd/mm/yyyy'),'dd/mm/yyyy')";
            sql2 += " and to_timestamp(to_char(ntc.denngay,'dd/mm/yyyy'),'dd/mm/yyyy') then true else false end";
            sql2 += " or case when (ntc.denngay is null and to_timestamp(to_char(ntc.tungay,'dd/MM/yyyy'),'dd/MM/yyyy')";
            sql2 += " < to_timestamp('" + ngay_lv + "','dd/MM/yyyy')) then true else false end)";
            sql2 += " and ntc.madv_tc = '" + s_bophan.Trim() + "' ";
            sql2 += " order by ntc.manv";
            return mDB.get_data(sql2);
        }
        private void load_datagrid()
        {
            string m_col = "";
            sql = "select  0 as stt,trim(l.ho)||' '||trim(l.ten) as hoten,l.phai,c.ten as chucdanh,d.*  ";
            sql+=" from "+m_user+".llnv l,"+m_user+".dmcd c,"+m_user+".chamcong_thuongtruc_"+s_mmyy+" d  ";
            sql+=" where l.macd=c.ma and l.manv=d.manv and d.mmyy='"+s_mmyy+"' ";
            if (s_bophan != "")
                sql += " and d.madv='" + s_bophan + "'";
            if (bcctc_taikhoa)
            {
                sql += " and d.manv not in (select l.manv from " + m_user + ".llnv l inner join " + m_user + ".hopdong hd on l.manv=hd.manv";
                sql += " inner join " + m_user + ".max_lan ml on hd.manv=ml.manv";
                sql += " inner join " + m_user + ".nv_tangcuong_" + s_mmyy + " ntc on l.manv=ntc.manv";
                sql += " where hd.lan=ml.lan and hd.ngaynghi is null and to_timestamp(to_char(ntc.tungay,'dd/mm/yyyy'),'dd/mm/yyyy') =";
                sql += " (select max(to_timestamp(to_char(tc.tungay,'dd/mm/yyyy'),'dd/mm/yyyy')) from " + m_user + ".nv_tangcuong_" + s_mmyy + " tc where ntc.manv=tc.manv)";
                sql += " and (case ntc.denngay is not null when to_timestamp('" + s_ngaylv + "','dd/mm/yyyy')";
                sql += " between to_timestamp(to_char(ntc.tungay,'dd/mm/yyyy'),'dd/mm/yyyy')";
                sql += " and to_timestamp(to_char(ntc.denngay,'dd/mm/yyyy'),'dd/mm/yyyy') then true else false end";
                sql += " or";
                sql += " case when (ntc.denngay is null and to_timestamp(to_char(ntc.tungay,'dd/MM/yyyy'),'dd/MM/yyyy') < to_timestamp('" + s_ngaylv + "','dd/MM/yyyy'))";
                sql += " then true else false end) and ntc.madv_tc <> '" + s_bophan + "'  order by ntc.manv)";
            }
            sql += " order by c.thutu";
            sql1 = " select * from " + m_user + ".dmlydo_phep where nhombc='TR' order by stt";
            ds = mDB.get_data(sql, sql1);
            if (bcctc_taikhoa)
            {
                ds_tcden = get_nvtangcuong_den(s_ngaylv.Trim());
                ds.Merge(ds_tcden);
            }
            int i = 0;
            if (ds.Tables[0].Rows.Count > 0)
            {                
                foreach (DataRow r in ds.Tables[0].Rows)
                {
                    i++;
                    r["stt"] = i;
                    int t = mDB.get_ngaytrongthang(p_thang, p_nam);
                    for (int d = 1; d <= mDB.get_ngaytrongthang(p_thang, p_nam); d++)
                    {
                        string str = r["n" + d.ToString() + ""].ToString();
                        if (str.Trim() != "")
                        {
                            DataRow[] dr = ds.Tables[1].Select("ma = '" + str.Trim() + "' ");
                            r["n" + d.ToString() + ""] = dr[0]["kihieu_phep"].ToString();
                        }
                        else
                        {
                            r["n" + d.ToString() + ""] = "";
                        }
                    }
                }
            }
            else
            {
                MessageBox.Show(lan.Change_language_MessageText("Không có nhân viên"),AccessData.Msg, MessageBoxButtons.OK);
            }
            dataGridchamcong.DataSource = ds.Tables[0];
            CurrencyManager cm = (CurrencyManager)BindingContext[dataGridchamcong.DataSource, dataGridchamcong.DataMember];
            DataView dv = (DataView)cm.List;
            dv.AllowNew = false;
            dv.AllowDelete = false;
            if (m_songay < 31)
            {
                for (i = m_songay+1; i <= 31; i++)
                {
                    m_col = "n" + i.ToString();
                    dataGridchamcong.Columns[m_col].Visible = false;
                }
            }                 
            txtTong.Text = ds.Tables[0].Rows.Count.ToString();
        }
        private void cmdThoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        private void cmdChonkp_Click(object sender, EventArgs e)
        {
            cboKhoa.Enabled = true;
            SendKeys.Send("{F4}");
            cboKhoa.Focus();
        }
        private void cboKhoa_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {                
                SendKeys.Send("{Tab}");
            }
        }
        private void cboKhoa_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.ActiveControl == cboKhoa)
            {
                if (cboKhoa.SelectedIndex != -1)
                {
                    s_bophan = cboKhoa.SelectedValue.ToString();
                    load_datagrid();
                }
            }
        }
        private void cboKhoa_Validated(object sender, EventArgs e)
        {
            if (cboKhoa.SelectedIndex != -1)
            {
                s_bophan = cboKhoa.SelectedValue.ToString();
                load_datagrid();
                cboKhoa.Enabled = false;
            }
        }
        #region kiem tra
        private int kt_khuvuc(string madv)
        {
            string str = mDB.get_madv_theokv("HS");
            int flag = 0;
            string[] arr = str.Split(',');
            for (int i = 0; i < arr.Length; i++)
            {
                if (madv.Trim() == arr[i].ToString().Trim())
                {
                    flag = 1;
                    break;
                }
                else
                {
                    flag = 0;
                }
            }
            return flag;
        }
        private int kt_ngayle(string ngay)
        {
            int flag = 0;
            foreach (DataRow dr in ds_ngayle.Tables[0].Rows)
            {
                if (ngay.Trim() == dr["ngay"].ToString().Trim())
                {
                    flag = 1;
                    break;
                }
                else
                {
                    flag = 0;
                }
            }
            return flag;
        }
        private int kt_ngaybule(string ngay)
        {
            int flag = 0;
            foreach (DataRow dr in ds_ngaybule.Tables[0].Rows)
            {
                if (ngay.Trim() == dr["ngay"].ToString().Trim())
                {
                    flag = 1;
                    break;
                }
                else
                {
                    flag = 0;
                }
            }
            return flag;
        }
        #endregion
       
        private void cmdSave_Click(object sender, EventArgs e)
        {
            string m_value = "",s_manv="",s_mmyy="";
            cmdChonkp.Enabled = m_admin;
            DataSet dsTmp = new DataSet();        
            DateTime d_ngay = mDB.StringToDateTime(s_ngaylv);
            s_mmyy = d_ngay.Month.ToString().PadLeft(2, '0') + d_ngay.Year.ToString().Substring(2, 2);
            if (kt_khuvuc(s_bophan) == 1) //1 la hscc
            {
                if (kt_ngayle(s_ngaylv) == 1)
                {
                    m_value = "HL";                   
                }
                else
                {
                    if (kt_ngaybule(s_ngaylv) == 1)
                    {
                        m_value = "HN";
                    }
                    else
                    {
                        if (mDB.cc_cuoituan(s_mmyy))
                        {
                            if (d_ngay.DayOfWeek == DayOfWeek.Sunday)
                            {
                                m_value = "HN";
                            }                            
                            else m_value = "HT";
                        }
                        else
                        {
                            if (d_ngay.DayOfWeek == DayOfWeek.Saturday || d_ngay.DayOfWeek == DayOfWeek.Sunday)
                            {
                                m_value = "HN";
                            }
                            else m_value = "HT";
                        }
                    }
                }
            }
            else
            {
                if (kt_khuvuc(s_bophan) == 0)
                {
                   if (kt_ngayle(s_ngaylv) == 1)
                    {
                        m_value = "TL";                       
                    }
                    else 
                    {
                        if (kt_ngaybule(s_ngaylv) == 1)
                        {
                            m_value = "TN";
                        }
                        else
                        {
                            if (mDB.cc_cuoituan(s_mmyy))
                            {
                                if (d_ngay.DayOfWeek == DayOfWeek.Sunday)
                                {
                                    m_value = "TN";
                                }
                                else m_value = "TT";
                            }
                            else
                            {
                                if (d_ngay.DayOfWeek == DayOfWeek.Saturday || d_ngay.DayOfWeek == DayOfWeek.Sunday)
                                {
                                    m_value = "TN";
                                }
                                else m_value = "TT";
                            }
                        }
                    }
                }
            }
            s_manv = txtManv.Text.Trim();            
                sql = "update " + m_user + ".chamcong_thuongtruc_" + s_mmyy + " set " + m_hh + "='" + m_value + "' where manv='" + s_manv + "' and mmyy='" + s_mmyy + "'";
                mDB.Execute_data(sql);             
            load_datagrid();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            this.BindingContext[ds.Tables[0]].Position += 1;
            this.Update();
        }
        private void txtManv_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }
        private void txtHoten_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }
        private void txtPhai_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }
        private void cmdTiep_Click(object sender, EventArgs e)
        {
            this.BindingContext[ds.Tables[0]].Position += 1;
            this.Update();
        }
        private void cmdTruoc_Click(object sender, EventArgs e)
        {
            this.BindingContext[ds.Tables[0]].Position += -1;
            this.Update();
        }      
        private void cmdBoqua_Click(object sender, EventArgs e)
        {
            foreach (DataRow r in ds.Tables[0].Rows)
            {
                string m_value = "", s_manv = "", s_mmyy = "";
                cmdChonkp.Enabled = m_admin;
                DataSet dsTmp = new DataSet();
                DateTime d_ngay = mDB.StringToDateTime(s_ngaylv);
                s_mmyy = d_ngay.Month.ToString().PadLeft(2, '0') + d_ngay.Year.ToString().Substring(2, 2);
                m_value = "";
                s_manv = r["manv"].ToString().Trim();
                sql = "update " + m_user + ".chamcong_thuongtruc_" + s_mmyy + " set " + m_hh + "='" + m_value + "' where manv='" + s_manv + "' and madv='" + s_bophan + "' and mmyy='" + s_mmyy + "'";
                mDB.Execute_data(sql);               
            }
            load_datagrid();
                
        }
        private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            dataGridchamcong.Rows[e.RowIndex].ErrorText = String.Empty;
        }
        private void chk_letet_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                SendKeys.Send("{Tab}");
        }
        private void chk_hscc_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
                cmdSave.Focus();
        }
        private void btn_xoa_Click(object sender, EventArgs e)
        {
            string m_value = "", s_manv = "", s_mmyy = "";
            cmdChonkp.Enabled = m_admin;
            DataSet dsTmp = new DataSet();
            DateTime d_ngay = mDB.StringToDateTime(s_ngaylv);
            s_mmyy = d_ngay.Month.ToString().PadLeft(2, '0') + d_ngay.Year.ToString().Substring(2, 2);
            m_value = "";
            s_manv = txtManv.Text.Trim();
            sql = "update " + m_user + ".chamcong_thuongtruc_" + s_mmyy + " set " + m_hh + "='" + m_value + "' where manv='" + s_manv + "' and madv='" + s_bophan + "' and mmyy='" + s_mmyy + "'";
            mDB.Execute_data(sql);
            load_datagrid();
        }
        private void txtngay_Validated(object sender, EventArgs e)
        {
            ds_ngayle = mDB.get_ngaynghile(0, s_mmyy);
            ds_ngaybule = mDB.get_ngaynghile(1, s_mmyy);
        }
        private void dataGridchamcong_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                DataRowView arv = (DataRowView)(dataGridchamcong.CurrentRow.DataBoundItem);
                txtManv.Text = arv["manv"].ToString();
                txtHoten.Text = arv["hoten"].ToString();
                txtPhai.Text = arv["phai"].ToString();
            }
            catch
            {
            }
        }
        private void dataGridchamcong_CurrentCellChanged(object sender, EventArgs e)
        {
           dataGridchamcong_CellClick(null, null);
        } 
    }
}